﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Web.Security;

namespace NCC_SOP.app_codes
{
    public class dbConnection
    {
        public string connectionStr = "server=localhost;database=ncc-sop;uid=sa;password=nowayy123;";

        public void createNewRole(string roleName)
        {
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.createNewRole, SqlConnection);
                SqlConnection.Open();
                cmd.Parameters.Add("@roleName", SqlDbType.VarChar);
                cmd.Parameters["@roleName"].Value = roleName;
                cmd.ExecuteNonQuery();
            }
        }

        public string[] getAllRoles()
        {
            ArrayList roles = new ArrayList();
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getAllRoles, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    roles.Add(reader["roleName"].ToString());
                }
            }
            return (string[])roles.ToArray(System.Type.GetType("System.String"));
        }

        public string[] getAllDepartments()
        {
            ArrayList departments = new ArrayList();
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getAllDepartments, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    departments.Add(reader["alias"].ToString());
                }
            }
            return (string[])departments.ToArray(System.Type.GetType("System.String"));
        }

        public string[] getAllUsers()
        {
            MembershipUserCollection muc = Membership.GetAllUsers();
            ArrayList users = new ArrayList();
            foreach (MembershipUser mu in muc)
            {
                users.Add(mu.UserName);
            }
            return (string[])users.ToArray(System.Type.GetType("System.String"));
        }

        public string[] getUsersInReportRole(string roleName)
        {
            string roleID = getRoleID(roleName);
            ArrayList userIDs = new ArrayList();
            ArrayList userNames = new ArrayList();

            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getUsersInReportRole1, SqlConnection);
                cmd.Parameters.Add("@roleID", SqlDbType.VarChar);
                cmd.Parameters["@roleID"].Value = roleID;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    userIDs.Add(reader["UserId"].ToString());
                }
                reader.Close();
                string[] IDs = (string[])userIDs.ToArray(System.Type.GetType("System.String"));
                cmd.CommandText = sqlQuery.getUsersinReportRole2;
                cmd.Parameters.Add("@userID", SqlDbType.VarChar);
                foreach (string id in IDs)
                {
                    cmd.Parameters["@userID"].Value = id;
                    userNames.Add(cmd.ExecuteScalar().ToString());
                }
            }

            return (string[])userNames.ToArray(System.Type.GetType("System.String"));
        }

        public string[] getReportRolesForUser(string userName)
        {
            string userID = getUserID(userName);
            ArrayList roleIDs = new ArrayList();
            ArrayList roles = new ArrayList();

            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getReportRolesForUser1, SqlConnection);
                cmd.Parameters.Add("@userID", SqlDbType.VarChar);
                cmd.Parameters["@userID"].Value = userID;
                SqlConnection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    roleIDs.Add(reader["roleID"].ToString());
                }
                reader.Close();
                string[] IDs = (string[])roleIDs.ToArray(System.Type.GetType("System.String"));
                cmd.CommandText = sqlQuery.getReportRolesForUser2;
                cmd.Parameters.Add("@roleID", SqlDbType.Int);
                foreach (string id in IDs)
                {
                    cmd.Parameters["@roleID"].Value = id;
                    roles.Add(cmd.ExecuteScalar().ToString());
                }
            }

            return (string[])roles.ToArray(System.Type.GetType("System.String"));
        }

        public string[] getDepartmentsForUser(string userName)
        {
            string user = getUserID(userName);
            Guid userID = new Guid(user);
            ArrayList departmentIDs = new ArrayList();
            ArrayList departments = new ArrayList();

            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getDepartmentsForUser1, SqlConnection);
                cmd.Parameters.Add("@userID", SqlDbType.UniqueIdentifier);
                cmd.Parameters["@userID"].Value = userID;
                SqlConnection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    departmentIDs.Add(reader["departmentID"].ToString());
                }
                reader.Close();
                string[] IDs = (string[])departmentIDs.ToArray(System.Type.GetType("System.String"));
                cmd.CommandText = sqlQuery.getDepartmentsForUser2;
                cmd.Parameters.Add("@departmentID", SqlDbType.Int);
                foreach (string id in IDs)
                {
                    cmd.Parameters["@departmentID"].Value = id;
                    departments.Add(cmd.ExecuteScalar().ToString());
                }
            }

            return (string[])departments.ToArray(System.Type.GetType("System.String"));
        }

        private string getUserID(string userName)
        {
            string userID = "";
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getUserID, SqlConnection);
                cmd.Parameters.Add("@userName", SqlDbType.VarChar);
                cmd.Parameters["@userName"].Value = userName;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    userID = (reader["userID"].ToString());
                }                
            }
            return userID;
        }

        public string getRoleID(string role)
        {
            string roleID = "";
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getRoleID, SqlConnection);
                cmd.Parameters.Add("@role", SqlDbType.VarChar);
                cmd.Parameters["@role"].Value = role;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    roleID = (reader["roleID"].ToString());
                }  
                
            }
            return roleID;
        }

        public string getDepartmentID(string alias)
        {
            string departmentID = "";
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getDepartmentID, SqlConnection);
                cmd.Parameters.Add("@alias", SqlDbType.VarChar);
                cmd.Parameters["@alias"].Value = alias;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    departmentID = (reader["departmentID"].ToString());
                }

            }
            return departmentID;
        }

        // add 1 user into many roles
        public void addUserIntoReportRoles(string userName, string[] roles)
        {
            foreach (string role in roles)
            {
                addUserIntoReportRole(userName, role);
            }
        }

        // add many users into many roles
        public void addUsersIntoReportRoles(string[] userNames, string[] roles)
        {
            foreach (string user in userNames)
            {
                addUserIntoReportRoles(user, roles);
            }
        }

        // add many users into 1 role
        public void addUsersIntoReportRole(string[] userNames, string role)
        {
            foreach (string userName in userNames)
            {
                addUserIntoReportRole(userName, role);
            }
        }

        // add 1 user into 1 role
        public void addUserIntoReportRole(string userName, string role)
        {
            string userID = getUserID(userName);
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.addUserIntoReportRole, SqlConnection);
                cmd.Parameters.Add("@userID", SqlDbType.VarChar);
                cmd.Parameters.Add("@roleID", SqlDbType.Int);
                cmd.Parameters["@userId"].Value = userID;
                cmd.Parameters["@roleId"].Value = getRoleID(role);

                SqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        // remove 1 user from many roles
        public void removeUserFromReportRoles(string userName, string[] roles)
        {
            foreach (string role in roles)
            {
                removeUserFromReportRole(userName, role);
            }
        }

        // remove many users from many roles
        public void removeUsersFromReportRoles(string[] userNames, string[] roles)
        {
            foreach (string user in userNames)
            {
                removeUserFromReportRoles(user, roles);
            }
        }

        // remove 1 user from 1 role
        public void removeUserFromReportRole(string userName, string role)
        {
            string userID = getUserID(userName);
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.removeUserFromReportRole, SqlConnection);
                cmd.Parameters.Add("@userID", SqlDbType.VarChar);
                cmd.Parameters.Add("@roleID", SqlDbType.Int);
                cmd.Parameters["@userId"].Value = userID;
                cmd.Parameters["@roleId"].Value = getRoleID(role);

                SqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        // remove many users from 1 role
        public void removeUsersFromReportRole(string[] userNames, string role)
        {
            foreach (string userName in userNames)
            {
                removeUserFromReportRole(userName, role);
            }
        }

        public bool roleExists(string role)
        {
            string[] roles = getAllRoles();
            foreach (string existingRole in roles)
            {
                if (existingRole.Equals(role))
                {
                    return true;
                }
            }
            return false;
        }

        public void deleteRole(string role)
        {
            string roleID;
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.deleteRole1, SqlConnection);
                cmd.Parameters.Add("@role", SqlDbType.VarChar);
                cmd.Parameters["@role"].Value = role;
                SqlConnection.Open();
                roleID = cmd.ExecuteScalar().ToString();
                cmd.Parameters.Add("@roleID", SqlDbType.VarChar);
                cmd.Parameters["@roleID"].Value = roleID;
                cmd.CommandText = sqlQuery.deleteRole2;
                cmd.ExecuteNonQuery();
                cmd.CommandText = sqlQuery.deleteRole3;
                cmd.ExecuteNonQuery();
                cmd.CommandText = sqlQuery.deleteRole4;
                cmd.ExecuteNonQuery();
            }
        }

        public int[] getAllHotSpotIDs()
        {
            ArrayList IDs = new ArrayList();
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getAllHotSpotIDs, SqlConnection);
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    IDs.Add(reader["p_id"]);
                }
            }
            return (int[])IDs.ToArray(System.Type.GetType("System.Int32"));
        }

        public DataTable getHotSpotDetails(int id)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("LeftOrRight", Type.GetType("System.String"));
            dt.Columns.Add("Label", Type.GetType("System.String"));
            dt.Columns.Add("ImgRight", Type.GetType("System.String"));
            dt.Columns.Add("ImgLeft", Type.GetType("System.String"));
            dt.Columns.Add("ImgTop", Type.GetType("System.String"));
            dt.Columns.Add("ImgBtm", Type.GetType("System.String"));
            dt.Columns.Add("TextRight", Type.GetType("System.String"));
            dt.Columns.Add("TextLeft", Type.GetType("System.String"));
            dt.Columns.Add("TextTop", Type.GetType("System.String"));
            dt.Columns.Add("TextBtm", Type.GetType("System.String"));
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getHotSpotDetails, SqlConnection);
                cmd.Parameters.Add("@p_id", SqlDbType.Int);
                cmd.Parameters["@p_id"].Value = id;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DataRow dr = dt.NewRow();
                    dr["LeftOrRight"] = reader["LeftOrRight"];
                    dr["Label"] = reader["Label"];
                    dr["ImgLeft"] = reader["ImgLeft"];
                    dr["ImgRight"] = reader["ImgRight"];
                    dr["ImgTop"] = reader["ImgTop"];
                    dr["ImgBtm"] = reader["ImgBtm"];
                    dr["TextLeft"] = reader["TextLeft"];
                    dr["TextRight"] = reader["TextRight"];
                    dr["TextTop"] = reader["TextTop"];
                    dr["TextBtm"] = reader["TextBtm"];
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        public string getSelectedCancer(int id)
        {
            string cancer;
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getSelectedCancer, SqlConnection);
                cmd.Parameters.Add("@p_id", SqlDbType.Int);
                cmd.Parameters["@p_id"].Value = id;
                SqlConnection.Open();
                cancer = cmd.ExecuteScalar().ToString();
            }
            return cancer;
        }

        public string[] getHistologyList(String cancer)
        {
            ArrayList histology = new ArrayList();
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getHistologyList, SqlConnection);
                cmd.Parameters.Add("@label", SqlDbType.VarChar);
                cmd.Parameters["@label"].Value = cancer;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    histology.Add(reader["Detail_Description"]);
                }
            }
            return (string[])histology.ToArray(System.Type.GetType("System.String"));
        }

        public DataTable getStaging(String cancer, String version)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Staging", Type.GetType("System.String"));
            dt.Columns.Add("Type", Type.GetType("System.String"));
            dt.Columns.Add("Detail", Type.GetType("System.String"));
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getStaging, SqlConnection);
                cmd.Parameters.Add("@ca", SqlDbType.VarChar);
                cmd.Parameters["@ca"].Value = cancer;
                cmd.Parameters.Add("@ver", SqlDbType.VarChar);
                cmd.Parameters["@ver"].Value = version;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DataRow dr = dt.NewRow();
                    dr["Staging"] = reader["staging"];
                    dr["Type"] = reader["type"];
                    dr["Detail"] = reader["detail"];
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        public string[] getStagingTypes(String cancer)
        {
            ArrayList stagingTypes = new ArrayList();
            using (SqlConnection SqlConnection = new SqlConnection(connectionStr))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery.getStagingTypes, SqlConnection);

                cmd.Parameters.Add("@ca", SqlDbType.VarChar);
                cmd.Parameters["@ca"].Value = cancer;
                SqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    stagingTypes.Add(reader["type"]);
                }
            }
            return (string[])stagingTypes.ToArray(System.Type.GetType("System.String"));
        }
    }
}